if exists (select * from dbo.sysobjects where id =
object_id(N'[springofwat].[sp_GetPosts]') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [springofwat].[sp_GetPosts]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

setuser N'springofwat'
GO


CREATE PROCEDURE sp_GetPosts @UserID          int
                            ,@Year            int
                            ,@Month           int
                            ,@Day             int
                            ,@StartIndex      int
                            ,@NumberOfRecords int
                            ,@Type           
varchar(10)
                            ,@Status          varchar
(10)
                            ,@CategoryID      int
                            ,@OrderBy        
varchar(20)
                            ,@TotalRecords    int
output
AS
   DECLARE @SelectSQL  VARCHAR(4000)
          ,@WHERE      INT 

   SELECT @WHERE = 0
   SELECT @SelectSQL = 'SELECT * FROM ForumPosts'

   IF ISNULL(@UserID, 0) > 0
   BEGIN
      SELECT @SelectSQL = @SelectSQL + ' WHERE UserId
= ' + CAST(@UserID AS VARCHAR(20))
      SET @WHERE = 1
   END

   IF ISNULL(@Year, 0) > 0
      IF @WHERE = 0
      BEGIN
         SELECT @SelectSQL = @SelectSQL + ' WHERE
YEAR(CreateDate) = ' + CAST(@Year AS VARCHAR(20))
         SET @WHERE = 1
      END
      ELSE
      BEGIN
         SELECT @SelectSQL = @SelectSQL + ' AND
YEAR(CreateDate) = ' + CAST(@Year AS VARCHAR(20))
      END

   IF ISNULL(@Month, 0) > 0
      IF @WHERE = 0
      BEGIN
         SELECT @SelectSQL = @SelectSQL + ' WHERE
MONTH(CreateDate) = ' + CAST(@Month AS VARCHAR(20))
         SET @WHERE = 1
      END
      ELSE
      BEGIN
         SELECT @SelectSQL = @SelectSQL + ' AND
MONTH(CreateDate) = ' + CAST(@Month AS VARCHAR(20))
      END

   IF ISNULL(@Day, 0) > 0
      IF @WHERE = 0
      BEGIN
         SELECT @SelectSQL = @SelectSQL + ' WHERE
DAY(CreateDate) = ' + CAST(@Day AS VARCHAR(20))
         SET @WHERE = 1
      END
      ELSE
      BEGIN
         SELECT @SelectSQL = @SelectSQL + ' AND
DAY(CreateDate) = ' + CAST(@Day AS VARCHAR(20))
      END

   IF ISNULL(@CategoryId, 0) > 0
      IF @WHERE = 0
      BEGIN
         SELECT @SelectSQL = @SelectSQL + ' WHERE
CategoryId = ' + CAST(@CategoryId AS VARCHAR(20))
         SET @WHERE = 1
      END
      ELSE
      BEGIN
         SELECT @SelectSQL = @SelectSQL + ' AND
CategoryId = ' + CAST(@CategoryId AS VARCHAR(20))
      END

   IF LEN(ISNULL(@Status, '')) > 0
      IF @WHERE = 0
      BEGIN
         SELECT @SelectSQL = @SelectSQL + ' WHERE
Status = "' + @Status + '"'
         SET @WHERE = 1
   END
   ELSE
      SELECT @SelectSQL = @SelectSQL + ' AND Status
="' + @Status + '"'

   IF LEN(ISNULL(@Type, '')) > 0
      IF @WHERE = 0
      BEGIN
         SELECT @SelectSQL = @SelectSQL + ' WHERE Type
= ''' + @Type + '''' 
         SET @WHERE = 1
      END
      ELSE
         SELECT @SelectSQL = @SelectSQL + ' AND Type =
''' + @Type + ''''

   SELECT @SelectSQL = @SelectSQL + ' AND
(parentpostid is null OR parentpostid =0) Order By ' +
@OrderBy


   /* This is bad that we have to list all the column
definition here */
   /* I tried to use 'select into #tempposts from
forumposts' and    */
   /* 'alter table #tempposts alter column postid int
(4)' but didn't  */
   /* work.                                           
                                        */
   /* The main idea is to add a new identity column
rownum. That*/
   /* is all because T-SQL does not provide rownum
column        */

   create table #tempposts(rownum int identity(1,1)
                          ,PostID  int
                          ,CategoryID int
                          ,ParentPostID int
                          ,Subject nvarchar(400)
                          ,Body ntext
                          ,CreateDate datetime
                          ,Status varchar(10)
                          ,Summary nvarchar(2000)
                          ,Type varchar(50)
                          ,UserID int
                          ,NumberOfVisits int
                          ,PartnerID int
                          ,OriginAuthor nvarchar(400)
                          ,URL varchar(1000)
                          ,OriginalDate datetime
                          ,LastModifiedBy int
                          ,LastModifiedDateTime
datetime)

   insert into #tempposts(PostID
                         ,CategoryID
                         ,ParentPostID
                         ,Subject
                         ,Body
                         ,CreateDate
                         ,Status
                         ,Summary
                          ,PartnerID
                         ,OriginAuthor
                         ,URL
                         ,OriginalDate
                         ,LastModifiedBy
                         ,LastModifiedDateTime
                          ,Type
		,NumberOfVisits
                         ,UserID)
                     EXEC(@SelectSQL)

   select @TotalRecords = count(*) from #tempposts

   if (@StartIndex = 0 or @NumberOfRecords = 0)
      select *
      from   #tempposts
      left join ForumCategories
          on #tempposts.CategoryID =
ForumCategories.CategoryID
      left join Users
          on #tempposts.UserID = Users.UserID
      left join Partner
         on #tempposts.PartnerID = Partner.PartnerID
   else
      select  *
      from   #tempposts
      left join ForumCategories
          on #tempposts.CategoryID =
ForumCategories.CategoryID
      left join Users
          on #tempposts.UserID = Users.UserID
        left join Partner
         on #tempposts.PartnerID = Partner.PartnerID
      where  rownum >= @StartIndex
      And    rownum <  @StartIndex + @NumberOfRecords

   Drop table #tempposts
GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
